package com.are.odisea;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ManejadorNoticias {
	private db conexion = null;
	private Noticias not;
	
	public static final int ORDER_ID = 1;
	public static final int ORDER_FEC_PUB = 2;
	public static final int ORDER_CAT = 3;

	public ManejadorNoticias(db conexion) {
		super();
		this.setConexion(conexion);
		this.setNot(new Noticias());
	}
	public ManejadorNoticias(db conexion, Noticias not) {
		super();
		this.setConexion(conexion);
		this.setNot(not);
	}
	public db getConexion() {
		return conexion;
	}
	public void setConexion(db conexion) {
		this.conexion = conexion;
	}
	public Noticias getNot() {
		return not;
	}
	public void setNot(Noticias not) {
		this.not = not;
	}
	
	public boolean add() throws SQLException{
		boolean result = false;

			String sql = "insert into noticias (titulo,cuerpo,fec_pub,fec_fin,usuario,categoria,estado) values (?,?,?,?,?,?,?)";
			java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
			pst.setString(1, not.getTitulo());
			pst.setString(2, not.getCuerpo());
			pst.setString(3, not.getFec_pub());
			pst.setString(4, not.getFec_fin());
			pst.setString(5, not.getUsuario());
			pst.setString(6, not.getCategoria());
			pst.setString(7, not.getEstado());
			if (conexion.Update(pst) > 0) {
				conexion.Commit();
				result = true;
			}
		return result;
	}
	
	public boolean update(String key) throws SQLException{
		boolean result = false;

			String sql = "update noticias set titulo= ?, cuerpo=?,fec_pub=?,fec_fin=?, usuario=?, categoria=?, estado=? where id=?";
			
			java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
			pst.setString(1, not.getTitulo());
			pst.setString(2, not.getCuerpo());
			pst.setString(3, not.getFec_pub());
			pst.setString(4, not.getFec_fin());
			pst.setString(5, not.getUsuario());
			pst.setString(6, not.getCategoria());
			pst.setString(7, not.getEstado());
			pst.setString(8, key);
			
			if (conexion.Update(pst) > 0) {
				conexion.Commit();
				result = true;
			}
			
		return result;
	}
	
	public boolean remove(String key) throws SQLException {
		boolean result = false;

			String sql = "delete from noticias where id=?";
			java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
			pst.setString(1,key);
			if (conexion.Update(pst) > 0) {
				conexion.Commit();
				result = true;
			}
			

		return result;	
	}
	
	
	public boolean exist(String key) throws SQLException {
		boolean result = false;

			String sql = "select * from noticias where id = ?";
			java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
			pst.setString(1,key);
			ResultSet rs = conexion.Query(pst);
			if (rs.next()) {
				result = true;
			}
			rs.close();
			
		
		return result;	
	}
	
	public boolean Find(String key) throws SQLException {
		boolean result = false;

			String sql = "select * from vnoticias where id=?";
			java.sql.PreparedStatement pst = conexion.getConnection().prepareStatement(sql);
			pst.setString(1,key);
			ResultSet rs = conexion.Query(pst);
			if (rs.next()) {
				not = new Noticias();
				not.setTitulo(rs.getString("titulo"));
				not.setCuerpo(rs.getString("cuerpo"));
				not.setFec_pub(rs.getString("fec_pub"));
				not.setFec_fin(rs.getString("fec_fin"));
				not.setUsuario(rs.getString("usuario"));
				not.setCategoria(rs.getString("categoria"));
				not.setEstado(rs.getString("estado"));
				not.setKey(rs.getString("id"));
				not.setNomcat(rs.getString("nomcat"));
				not.setNomusuario(rs.getString("nomusuario"));
				result = true;
			}
			rs.close();
		return result;	
	}
	
	public ArrayList<Noticias> list() throws SQLException {
		ArrayList<Noticias> notis = new ArrayList<Noticias>();
		
			String sql = "select * from vnoticias order by id";
			ResultSet rs  = conexion.Query(sql);
			while(rs.next()) {
				Noticias n = new Noticias();
				n.setTitulo(rs.getString("titulo"));
				n.setCuerpo(rs.getString("cuerpo"));
				n.setFec_pub(rs.getString("fec_pub"));
				n.setFec_fin(rs.getString("fec_fin"));
				n.setUsuario(rs.getString("usuario"));
				n.setCategoria(rs.getString("categoria"));
				n.setEstado(rs.getString("estado"));
				n.setKey(rs.getString("id"));
				n.setNomcat(rs.getString("nomcat"));
				n.setNomusuario(rs.getString("nomusuario"));
				notis.add(n);
			}
			rs.close();
		return notis;
	}
	
	public ArrayList<Noticias> list(int orden) throws SQLException {
		ArrayList<Noticias> notis = new ArrayList<Noticias>();

			String sql = "select * from vnoticias";
			
			switch(orden) {
			case ORDER_ID:
				sql += " order by id";
			break;
			case ORDER_CAT:
				sql += " order by categoria";
			break;
			case ORDER_FEC_PUB:
				sql += " order by fec_pub desc";
			break;
			}
			ResultSet rs  = conexion.Query(sql);
			while(rs.next()) {
				Noticias n = new Noticias();
				n.setTitulo(rs.getString("titulo"));
				n.setCuerpo(rs.getString("cuerpo"));
				n.setFec_pub(rs.getString("fec_pub"));
				n.setFec_fin(rs.getString("fec_fin"));
				n.setUsuario(rs.getString("usuario"));
				n.setCategoria(rs.getString("categoria"));
				n.setEstado(rs.getString("estado"));
				n.setKey(rs.getString("id"));
				n.setNomcat(rs.getString("nomcat"));
				n.setNomusuario(rs.getString("nomusuario"));
				notis.add(n);
			}
			rs.close();
		return notis;	
	}
	
	public ArrayList<Noticias> listMIsNot(String usu, int orden) throws SQLException {
		ArrayList<Noticias> notis = new ArrayList<Noticias>();

			String sql = "select * from Vnoticias where usuario='"+usu+"' and current_date between fec_pub and fec_fin";
			
			switch(orden) {
			case ORDER_ID:
				sql += " order by id";
			break;
			case ORDER_CAT:
				sql += " order by categoria";
			break;
			case ORDER_FEC_PUB:
				sql += " order by fec_pub desc";
			break;
			}
			ResultSet rs  = conexion.Query(sql);
			while(rs.next()) {
				Noticias n = new Noticias();
				n.setTitulo(rs.getString("titulo"));
				n.setCuerpo(rs.getString("cuerpo"));
				n.setFec_pub(rs.getString("fec_pub"));
				n.setFec_fin(rs.getString("fec_fin"));
				n.setUsuario(rs.getString("usuario"));
				n.setCategoria(rs.getString("categoria"));
				n.setEstado(rs.getString("estado"));
				n.setKey(rs.getString("id"));
				n.setNomcat(rs.getString("nomcat"));
				n.setNomusuario(rs.getString("nomusuario"));
				notis.add(n);
			}
			rs.close();
		return notis;	
	}
	public ArrayList<Noticias> listNotTer(String usu, int orden,String cat, String filtro) throws SQLException {
		ArrayList<Noticias> notis = new ArrayList<Noticias>();
		String sql="";
		if (cat==""){
			sql = "select * from Vnoticias where 1=0";
		}else if(cat.equals("-1")){
			sql = "select * from Vnoticias";
			sql+=" where (id in";
			sql+=" (select id from permisos_noticias where usuarios_id='"+usu+"')";
			sql+=" or (select rol from usuarios where id='"+usu+"') in (select rol from rol_noticia where id_not=id))"; 
			sql+=" and current_date between fec_pub and fec_fin and (titulo like '%"+filtro+"%' or cuerpo like '%"+filtro+"%')";
		}
		else{
			sql = "select * from Vnoticias";
			sql+=" where (id in";
			sql+=" (select id from permisos_noticias where usuarios_id='"+usu+"')";
			sql+=" or (select rol from usuarios where id='"+usu+"') in (select rol from rol_noticia where id_not=id))"; 
			sql+=" and current_date between fec_pub and fec_fin and categoria="+cat+" and (titulo like '%"+filtro+"%' or cuerpo like '%"+filtro+"%')";
		}
			
			
			switch(orden) {
			case ORDER_ID:
				sql += " order by id";
			break;
			case ORDER_CAT:
				sql += " order by categoria";
			break;
			case ORDER_FEC_PUB:
				sql += " order by fec_pub desc";
			break;
			}
			ResultSet rs  = conexion.Query(sql);
			while(rs.next()) {
				Noticias n = new Noticias();
				n.setTitulo(rs.getString("titulo"));
				n.setCuerpo(rs.getString("cuerpo"));
				n.setFec_pub(rs.getString("fec_pub"));
				n.setFec_fin(rs.getString("fec_fin"));
				n.setUsuario(rs.getString("usuario"));
				n.setCategoria(rs.getString("categoria"));
				n.setEstado(rs.getString("estado"));
				n.setKey(rs.getString("id"));
				n.setNomcat(rs.getString("nomcat"));
				n.setNomusuario(rs.getString("nomusuario"));
				notis.add(n);
			}
			rs.close();
		return notis;	
	}
	public ArrayList<Noticias> listNotTerIni(String usu, int orden) throws SQLException {
		ArrayList<Noticias> notis = new ArrayList<Noticias>();

			String sql = "select * from Vnoticias";
			sql+=" where (id in";
			sql+=" (select id from permisos_noticias where usuarios_id='1')";
			sql+=" or (select rol from usuarios where id='1') in (select rol from rol_noticia where id_not=id))"; 
			sql+=" and current_date between fec_pub and fec_fin order by fec_pub desc limit 0,5";
			ResultSet rs  = conexion.Query(sql);
			while(rs.next()) {
				Noticias n = new Noticias();
				n.setTitulo(rs.getString("titulo"));
				n.setCuerpo(rs.getString("cuerpo"));
				n.setFec_pub(rs.getString("fec_pub"));
				n.setFec_fin(rs.getString("fec_fin"));
				n.setUsuario(rs.getString("usuario"));
				n.setCategoria(rs.getString("categoria"));
				n.setEstado(rs.getString("estado"));
				n.setKey(rs.getString("id"));
				n.setNomcat(rs.getString("nomcat"));
				n.setNomusuario(rs.getString("nomusuario"));
				notis.add(n);
			}
			rs.close();
		return notis;	
	}
}
